Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO

Example 3–19 assumes you migrated your database to ORACLE using the CREATE RECID option, as discussed in the "ROWID Characteristics" section. This example and all subsequent examples show ROWID as being represented by the 4-byte integer value of the PROGRESS_RECID column as opposed to some other unique single-component index designated in your database to be the Progress recid.

Note: If you used a different single-component index to load the ROWID of a temp-table or native ROWID, you would need to map it accordingly, just as the example maps PROGRESS_RECID.

The RUN STORED-PROC command has no native awareness that the ORACLE Database Table is being queried for the result set(s) it generates. Therefore, to allow DataServer technology to convert the stored PROGRESS_RECID value into a native Progress ROWID value, the physical name of the target database table needs to be known. To achieve this bond, the temp-table that the stored procedure will populate must be associated with an OpenEdge ProDataSet object.

Example 3–19 shows a 4GL query filling the temp tables of a ProDataSet. It will be used as the baseline code which will be referenced throughout the remainder of this section.

DEFINE TEMP-TABLE ttCust LIKE Sports.Customer        FIELD tRecid AS INT DEFINE DATASET dsCust FOR ttCust. DEFINE VAR phDataSet AS HANDLE NO-UNDO. DEFINE QUERY qCust FOR Customer. phDataSet=DATASET dsCust:HANDLE. DEFINE DATA-SOURCE dsCust FOR QUERY qCust. BUFFER ttCust:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsCust:HANDLE,?,?,?). QUERY qCust:QUERY-PREPARE (“for each customer”). DATASET dsCust:FILL(). FOR EACH ttCust:    DISPLAY ttCust.name ttCust.tRecid. END.

Example 3–19: Simple ProDataSet code

Example 3–20 combines code from Example 3–18 and Example 3–19 by applying the results of the RUN STORED-PROC [LOAD-RESULT-INTO] technique, rather than a 4GL query, to fill the temp-table associated with a ProDataSets. Key points about Example 3–20 are presented following the example.

DEFINE VARIABLE rid-1 AS ROWID. DEFINE VARIABLE rid-2 AS ROWID. DEFINE TEMP-TABLE ttCust LIKE Sports.Customer        FIELD tRecid AS ROWID /* MUST BE CHANGED TO ROWID TYPE */ DEFINE VAR hSendSQL AS HANDLE EXTENT 1. hSendSQL[1]=TEMP-TABLE ttCust:HANDLE. DEFINE DATASET dsCust FOR ttCust. DEFINE VAR phDataSet AS HANDLE NO-UNDO. DEFINE QUERY qCust FOR Customer. phDataSet=DATASET dsCust:HANDLE. DEFINE DATA-SOURCE dsCust FOR QUERY qCust. BUFFER ttCust:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsCust:HANDLE,?,?,?). FIND FIRST Customer WHERE Customer.custnum=1 NO-LOCK. rid-1=ROWID(customer). /* populate the ttCust TempTable */ RUN STORED-PROC send-sql-statement    LOAD-RESULT-INTO hSendSQL (“select * from customer”). FIND FIRST ttCust WHERE ttCust.custnum=1 NO-LOCK. rid-2=ttCust.tRecid. If rid-1 <> rid-2 THEN MESSAGE “The same record but different ROWID’s”. IF rid-1=rid-2 THEN MESSAGE “Congratulations - we have the same ROWID’s”. MESSAGE string(ttCust.tRecid) VIEW-AS ALERT-BOX.

Example 3–20: Using the LOAD-RESULT-INTO technique to populate the underlying Temp-Table of a ProDataSet

Keep the following key points in mind as you review Example 3–20:

The TEMP-TABLE field that is mapped to the PROGRESS_RECID column should be changed from its standard definition of INTEGER to ROWID. In Example 3–20, the result column location where PROGRESS_RECID is being returned has been named tRecid in the temp-table. This renaming occurs in Example 3-26 because of the following line:

FIELD tRecid AS ROWID /* must be changed to ROWID type */ FIELD tRECID_ident AS INT.

The TEMP-TABLE must be defined to the ProDataSet. The following line, another excerpted line of code from Example 3–20, shows this definition:

DEFINE DATASET dsCust FOR ttCust.

The technique, demonstrated in Example 3–20, does not change the default behavior of the ROWID function, but provides a mechanism for the ROWID value to be stored along side its corresponding result rows; therefore, using the ROWID value to access database rows is unconventional with respect to the normal, or more typical, association between ROWID and a database table row. The following code demonstrates this difference.

Default use of ROWID function on a record buffer, as excerpted from Example 3–20:

FIND FIRST Customer WHERE Customer.custnum=1 NO-LOCK. rid-1=ROWID(customer).

In contrast, the following code, another excerpt from Example 3–20, demonstrates an alternative use of the ROWID value with a temp-table:

FIND FIRST Customer WHERE Customer.custnum=1 NO-LOCK. rid-2=ttCust.tRecid.